********Clean HH income and assets data from MxFLS3****************
*Note: Income data is sprinkled across several books, so need to compile

cd "$mxfls2rawdata"

*Initialize
cd "$mxfls3rawdata/hh09dta_b2"		
use ii_portad, clear
keep folio

tempfile hhbalsheetmex09
save "`hhbalsheetmex09'", replace

***Income and transfers into household

*Labor income and profits (from individual sections of survey)
cd "$mxfls3rawdata/hh09dta_b3a"	
use iiia_tb, clear

gen totallaborincome=0
*Primary job
replace totallaborincome=tb36a_2 if tb32p==3 | tb32p==4
replace totallaborincome=tb38p2_2 if tb32p==1 | tb32p==5 | tb32p==6
*Secondary job
replace totallaborincome=totallaborincome+tb36b_2 if tb32s==3 | tb32s==4
replace totallaborincome=totallaborincome+tb38s2_2 if tb32s==1 | tb32s==5 | tb32s==6
keep folio totallaborincome
collapse (sum) totallaborincome, by(folio)
la var totallaborincome "total yearly labor income from all HH members"
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergelip)
save "`hhbalsheetmex09'", replace

*Farm Profit
cd "$mxfls3rawdata/hh09dta_b2"	
use ii_su1.dta, clear
egen revenuefarm=rowtotal(su161_2 su162_2 su163_2)
keep folio revenuefarm
collapse (sum) revenuefarm, by(folio)
tempfile su1
save "`su1'"

cd "$mxfls3rawdata/hh09dta_b2"	
use ii_su.dta
egen expensefarm=rowtotal(su231 su232 su233 su234 su235 su236 su237 su238 su239 su2311_1)
keep folio expensefarm
merge 1:1 folio using "`su1'", nogen

gen totalprofithhfarm=revenuefarm-expensefarm
replace totalprofithhfarm=0 if totalprofithhfarm==.
label variable totalprofithhfarm "Profit from farm endeavors HH - past 12 months"
keep folio totalprofithhfarm
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergefp)
save "`hhbalsheetmex09'", replace

*Non-farm business profit
cd "$mxfls3rawdata/hh09dta_b2"	
use ii_nna1.dta, clear
gen profnonagric=0
replace profnonagric=nna22_11 if nna22_1==1
replace profnonagric=nna22_12 if nna22_1==2
replace profnonagric=-nna22_13 if nna22_1==3
collapse (sum) profnonagric, by(folio)
*Merge with nn to keep all hhs
merge 1:1 folio using ii_nna.dta, nogen
replace profnonagric=0 if profnonagric==.
la var profnonagric "Yearly non-farm business profit"
keep folio profnonagric
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergebp)
save "`hhbalsheetmex09'", replace


*Income from rental/interest of hh assets
cd "$mxfls3rawdata/hh09dta_b2"	
use ii_ah.dta, clear
egen ahvalrent=rowtotal(ah06*_2)
label variable ahvalrent "Income from rent of assets in past 12 months" 
keep folio ahvalrent
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergeai)
save "`hhbalsheetmex09'", replace

*Non-labor income from other sources
cd "$mxfls3rawdata/hh09dta_b2"	
use ii_in.dta, clear
egen intot=rowtotal(*_2 *_21)
label variable intot "Yearly total income from other sources-sell land, programs, scholarships"
keep folio intot
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergeoi)
save "`hhbalsheetmex09'", replace

*Transfers into HH
cd "$mxfls3rawdata/hh09dta_b3b"
use iiib_tp.dta, clear
egen trinpar=rowtotal(tp26a tp26b tp26c tp26f_21 tp26ma tp26mb tp26mc tp26mf_21 tp26pa tp26pb tp26pc tp26pf_21)
la var trinpar "Transfers in to hh from non-coresident parents"
keep folio trinpar
collapse (sum) trinpar, by(folio)
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergeinpar)
save "`hhbalsheetmex09'", replace

cd "$mxfls3rawdata/hh09dta_b3b"	
use iiib_th2.dta, clear
collapse (sum) th20b1 th20b2 th20b3 th20b4 th20b7_21 th20d1 th20d2 th20d3 th20d4 th20d7_21, by(folio pid_link)
egen trinsib=rowtotal(th20d1 th20d2 th20d3 th20d4 th20d7_21)
la var trinsib "Transfers in to hh from non-coresident siblings"
keep folio trinsib
collapse (sum) trinsib, by(folio)
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergeinsib)
save "`hhbalsheetmex09'", replace

cd "$mxfls3rawdata/hh09dta_b3b"	
use iiib_thi2.dta, clear
collapse (sum) thi24b1 thi24b2 thi24b3 thi24b4 thi24b7_21 thi24d1 thi24d2 thi24d3 thi24d4 thi24d7_21, by(folio pid_link)
egen trinchi=rowtotal(thi24d1 thi24d2 thi24d3 thi24d4 thi24d7_21)
la var trinchi "Transfers in to hh from non-coresident children"
keep folio trinchi
collapse (sum) trinchi, by(folio)
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergeinchi)
save "`hhbalsheetmex09'", replace

cd "$mxfls3rawdata/hh09dta_b3b"
use iiib_to.dta, clear
egen trinoth=rowtotal(to041 to042 to043 to044 to047_21)
la var trinoth "Transfers in to hh from others"
keep folio trinoth
collapse (sum) trinoth, by(folio)
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergeinoth)
save "`hhbalsheetmex09'", replace

*Total yearly income from all sources
egen totinc = rowtotal(totallaborincome totalprofithhfarm profnonagric ahvalrent intot trinpar trinsib trinchi trinoth)
la var totinc "Total household yearly income from all sources"
save "`hhbalsheetmex09'", replace

***Savings and investment 

*Savings
cd "$mxfls3rawdata/hh09dta_b3b"	
use iiib_cr.dta, clear
replace cr28=0 if cr27==3
collapse (sum) cr28, by(folio)
egen savi = rowtotal(cr28)
keep savi folio
la var savi "Total household yearly savings"
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergesav)
save "`hhbalsheetmex09'", replace

*Asset purchases/durables
cd "$mxfls3rawdata/hh09dta_b1"	
use i_cs1.dta, clear
egen consdurabs=rowtotal(cs27a_2 cs27b_2 cs27c_2 cs27f_2)
la var consdurabs "Purchases of durables"
keep consdurabs folio
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergedurs)
save "`hhbalsheetmex09'", replace


*Total savings from all sources
use "`hhbalsheetmex09'", clear
gen totsav=savi+consdurabs
la var totsav "Total household yearly savings/investment"
save "`hhbalsheetmex09'", replace


***Borrowing
cd "$mxfls3rawdata/hh09dta_b3b"	
use iiib_cr.dta, clear
collapse (sum) cr26_2, by(folio)
rename cr26_2 totbor
la var totbor "Total household yearly borrowing"
keep folio totbor
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergebor)
save "`hhbalsheetmex09'", replace


***Assets
cd "$mxfls3rawdata/hh09dta_b2"	
use ii_ah.dta

*By different asset categories
gen hhasssav=ah04h_2
gen hhassliv=ah04j_2+ah04k_2+ah04l_2
gen hhasspoul=ah04m_2

*House & land
gen hhasshouseland=ah04a_2
gen hhassothhouseland=ah04b_2


*Total assets
egen ahvaltot=rowtotal(ah04*_2)
la var ahvaltot "total HH assets all kinds"
keep folio hhass* ahvaltot
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergeass)
save "`hhbalsheetmex09'", replace


***Consumption


*HH total food consumption (imputed from weekly)
cd "$mxfls3rawdata/hh09dta_b1"	
use i_cs, clear
egen wexpout=rowtotal(cs02?_?2 )
la var wexpout "Weekly expenditure by hh outside home to purchase food (+tobacco)"
egen wexpin=rowtotal(cs04?_?2 )
la var wexpin "Weekly consumption of self-produced/received food"
egen hhfoodcons = rowtotal(wexpout wexpin)
replace hhfoodcons=hhfoodcons*52
la var hhfoodcons "Imputed (*52) total yearly consumption of food in hh (includes tobacco)"

*HH total nonfood consumption (imputed from monthly)
egen mexpoth=rowtotal(cs16?_2)
la var mexpoth "Monthly expenditure by hh outside home to purchase other items"
egen hhothcons=rowtotal(mexpoth cs18_2)
replace hhothcons=hhothcons*12
la var hhothcons "Imputed (*12) total yearly consumption of others + received/given gifts in hh"

keep folio hhfoodcons hhothcons
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergeconsfood)
save "`hhbalsheetmex09'", replace


*Clothes
cd "$mxfls3rawdata/hh09dta_b1"	
use i_cs1, clear
egen thrmonthmiscbuy=rowtotal(cs22?_2)
la var thrmonthmiscbuy "3 month purchase clothes"
egen thrmonthmiscprod=rowtotal(cs24?_2 cs26_2)
la var thrmonthmiscprod "3 month prod clothes / gifts received given"
egen hhclothcons=rowtotal(thrmonthmiscbuy thrmonthmiscprod)
replace  hhclothcons=hhclothcons*4
la var hhclothcons "Imputed (*4) total yearly consumption of clothes + received/given gifts in hh"

*Taxes/travel/gifts durables
egen expyrtrtax=rowtotal(cs27d_2 cs27e_2 cs29_2 cs31_2 cs34a_32)

*Education expenditures
egen edexp = rowtotal(cs34a_12 cs34a_22 cs34a_32 cs35a_12 cs35a_22 cs35a_32 cs36a_12 cs36a_22 cs36a_32)
la var edexp "Yearly HH expenditures on education"
keep folio hhclothcons expyrtrtax edexp cs20_2

merge 1:1 folio using "`hhbalsheetmex09'", gen (_mergehhcons1)
replace hhothcons=hhothcons+cs20_2*12
egen cons_wotransf=rowtotal(hhfoodcons hhothcons hhclothcons expyrtrtax edexp)
save "`hhbalsheetmex09'", replace

*Transfers out of HH
cd "$mxfls3rawdata/hh09dta_b3b"
use iiib_tp.dta, clear
egen troutpar=rowtotal(tp24a tp24b tp24c tp24f_21 tp24ma tp24mb tp24mc tp24mf_21 tp24pa tp24pb tp24pc tp24pf_21)
la var troutpar "Transfers out of hh to non-coresident parents"
collapse (sum) troutpar, by(folio)
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergeoutpar)
save "`hhbalsheetmex09'", replace 

cd "$mxfls3rawdata/hh09dta_b3b"
use iiib_th2.dta, clear
collapse (sum) th20b1 th20b2 th20b3 th20b4 th20b7_21 th20d1 th20d2 th20d3 th20d4 th20d7_21, by(folio pid_link)
egen troutsib=rowtotal(th20b1 th20b2 th20b3 th20b4 th20b7_21)
la var troutsib "Transfers out of hh to non-coresident siblings"
collapse (sum) troutsib, by(folio)
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergeoutsib)
save "`hhbalsheetmex09'", replace 

cd "$mxfls3rawdata/hh09dta_b3b"
use iiib_thi2.dta, clear
collapse (sum) thi24b1 thi24b2 thi24b3 thi24b4 thi24b7_21 thi24d1 thi24d2 thi24d3 thi24d4 thi24d7_21, by(folio pid_link)
egen troutchi=rowtotal(thi24b1 thi24b2 thi24b3 thi24b4 thi24b7_21)
la var troutchi "Transfers out of hh to non-coresident children"
collapse (sum) troutchi, by(folio)
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergeoutchi)
save "`hhbalsheetmex09'", replace 

cd "$mxfls3rawdata/hh09dta_b3b"
use iiib_to.dta, clear
egen troutoth=rowtotal(to021 to022 to023 to024 to027_21)
collapse (sum) troutoth, by(folio)
la var troutoth "Transfers out of hh to non-coresident others"
merge 1:1 folio using "`hhbalsheetmex09'", gen(_mergeoutoth)
save "`hhbalsheetmex09'", replace 

*Total consumption
use "`hhbalsheetmex09'", clear
egen totcons = rowtotal(cons_wotransf troutpar troutsib troutchi troutoth)
la var totcons "total HH yearly consumption, expenditures, and transfers out of the household"
save "`hhbalsheetmex09'", replace


save "`hhbalsheetmex09'", replace




